package com.iot.inclinometer.db;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.iot.inclinometer.util.Common;

public class DBHelper extends SQLiteOpenHelper {
    private static final String TAG = DBHelper.class.getSimpleName();
    /**
     * 默认文件路径：/data/data/com.*.*(package name)/目录下
     */
    private static final String DATABASE_NAME = "inclinometer.db";
    private static final int DATABASE_VERSION = 37;

    public DBHelper(Context context) {
        // CursorFactory设置为null,使用默认值
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    // 数据库第一次被创建时onCreate会被调用
    @Override
    public void onCreate(SQLiteDatabase db) {
        // 基本信息表
        String sql1 = "CREATE TABLE IF NOT EXISTS base_info "
                + "(base_id INTEGER PRIMARY KEY AUTOINCREMENT,project_name VARCHAR(64),item_name VARCHAR(64),drill_num VARCHAR(64),"
                + "depth VARCHAR(64),mark1 VARCHAR(64),mark2 VARCHAR(64),mark3 VARCHAR(64),mark4 VARCHAR(64),"
                + "loc VARCHAR(64),x VARCHAR(64),y VARCHAR(64),azimuth VARCHAR(64))";
        // 观测信息表
        String sql2 = "CREATE TABLE IF NOT EXISTS observe_info "
                + "(ob_id INTEGER PRIMARY KEY AUTOINCREMENT,observer VARCHAR(64),time VARCHAR(64),"
                + "interval VARCHAR(64),measurecount VARCHAR(64),slipdeep VARCHAR(64),base_id INTEGER,count INTEGER)";
        // 测量数据表
        String sql3 = "CREATE TABLE IF NOT EXISTS data_info "
                + "(data_id INTEGER PRIMARY KEY AUTOINCREMENT,depth VARCHAR(64),"
                + "lx VARCHAR(64),ly VARCHAR(64),error VARCHAR(64),theta_x VARCHAR(64),theta_y VARCHAR(64),"
                + "theta VARCHAR(64),ob_id INTEGER)";
        //修正数据表
        String sql4 = "CREATE TABLE IF NOT EXISTS correct_data_info "
                + "(data_id INTEGER PRIMARY KEY AUTOINCREMENT,depth VARCHAR(64),"
                + "lx VARCHAR(64),ly VARCHAR(64),error VARCHAR(64),theta_x VARCHAR(64),theta_y VARCHAR(64),"
                + "theta VARCHAR(64),ob_id INTEGER,"
                + "A0 VARCHAR(64),B0 VARCHAR(64),A180 VARCHAR(64),B180 VARCHAR(64))";
        //累加数据表
        String sql5 = "CREATE TABLE IF NOT EXISTS leiji_data_info "
                + "(leiji_id INTEGER PRIMARY KEY AUTOINCREMENT,depth VARCHAR(64),"
                + "lx VARCHAR(64),ly VARCHAR(64),theta_x VARCHAR(64),theta_y VARCHAR(64),"
                + "ob_id INTEGER)";
        //实时数据表
        String sql6 = "CREATE TABLE IF NOT EXISTS real_data_info "
                + "(real_id INTEGER PRIMARY KEY AUTOINCREMENT,theta_A VARCHAR(64),"
                + "theta_B VARCHAR(64),ltheta_A VARCHAR(64),ltheta_B VARCHAR(64),lltheta_A VARCHAR(64),"
                + "lltheta_B VARCHAR(64),currentMeasureTag VARCHAR(64),ob_id INTEGER)";

        try {
            db.execSQL(sql6);
            db.execSQL(sql5);
            db.execSQL(sql1);
            db.execSQL(sql2);
            db.execSQL(sql3);
            db.execSQL(sql4);

            // 创建索引

            String sql_idx_prjname_baseinfo = "CREATE INDEX IF NOT EXISTS idx_prjname_baseinfo ON base_info ( project_name)";
            String sql_idx_time_observeinfo = "CREATE INDEX IF NOT EXISTS idx_time_observeinfo ON observe_info (time)";

            db.execSQL(sql_idx_prjname_baseinfo);
            db.execSQL(sql_idx_time_observeinfo);
        } catch (Exception e) {
            Log.e(TAG, "数据库创建失败！" + e.getLocalizedMessage());
            Common.showMsg("本地数据库创建失败！");
        } finally {
        	// 不能关闭
//            if (null != db && db.isOpen()) {
//                db.close();
//            }
        }

        Log.i(TAG, "数据库创建成功！");
    }

    // 如果DATABASE_VERSION值被改为7,系统发现现有数据库版本不同,即会调用onUpgrade
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        //Log.i("info", "数据库更新成功！");
        //db.execSQL("DROP TABLE IF EXISTS base_info");
        //db.execSQL("DROP TABLE IF EXISTS observe_info");
        //db.execSQL("DROP TABLE IF EXISTS data_info");
        //	db.execSQL("DROP TABLE IF EXISTS correct_data_info");
        //	onCreate(db);
        //Log.i("info", "数据库更新成功！");
        //累计数据表
        String sql5 = "CREATE TABLE IF NOT EXISTS leiji_data_info "
                + "(leiji_id INTEGER PRIMARY KEY AUTOINCREMENT,depth VARCHAR(64),"
                + "lx VARCHAR(64),ly VARCHAR(64),theta_x VARCHAR(64),theta_y VARCHAR(64),"
                + "ob_id INTEGER)";
        db.execSQL(sql5);
        Log.i("info", "累积表更新成功！");
        //实时数据表
        String sql6 = "CREATE TABLE IF NOT EXISTS real_data_info "
                + "(real_id INTEGER PRIMARY KEY AUTOINCREMENT,theta_A VARCHAR(64),"
                + "theta_B VARCHAR(64),ltheta_A VARCHAR(64),ltheta_B VARCHAR(64),lltheta_A VARCHAR(64),"
                + "lltheta_B VARCHAR(64),currentMeasureTag VARCHAR(64),ob_id INTEGER)";
        db.execSQL(sql6);
        Log.i("info", "实时数据表更新成功！");
    }
}
